<?php

/*
 * La classe Stats serve per ricavare dati per le statistiche degli utenti e del forum
 */

/**
 * Description of Stats
 *
 * @author Stefano Poncini
 */
class Stats {
    
    /**
     * Metodo getPostsStats: serve per ricavare i dati relativi ai post
     * @return boolean|array
     */
    public static function getPostsStats(){
        KLogger::instance("log", KLogger::DEBUG)->logInfo("Caricamento statistiche messaggi");
        
        $post_stats = array();

        $sql = "select count(*) as mess_totali, round(count(*)/(DATEDIFF(max(creation_date),min(creation_date))),0) as messaggi_al_giorno,truncate(count(*)/(period_diff(date_format(max(creation_date), '%Y%m'), date_format(min(creation_date), '%Y%m'))),0) as messaggi_al_mese from post";

        $stmt = DB::getDBO()->prepare($sql);
        $stmt->execute();

        if($stmt->rowCount() == 1){
            $stats = $stmt->fetch();
            $post_stats['post_tot'] = $stats['mess_totali'];
            $post_stats['post_day'] = $stats['messaggi_al_giorno'];
            $post_stats['post_month'] = $stats['messaggi_al_mese'];
            // Carico i dati dell'utente nelle variabili di classe
            KLogger::instance("log", KLogger::DEBUG)->logInfo("Statistiche caricate");

        }else{
            
            return false;
        }
        return $post_stats;
        
    }
    
    /**
     * Metodo getForumStats: serve per ricavare statistiche relative al forum
     * @return boolean|array
     */
    public static function getForumStats(){
        KLogger::instance("log", KLogger::DEBUG)->logInfo("Caricamento statistiche forum ");
        
        $forum_stats = array();

        $sql = "select count(*) as tot_forum, (select count(*) from category) as tot_cat, (select count(*) from topic) as tot_topic from forum";

        $stmt = DB::getDBO()->prepare($sql);
        $stmt->execute();

        if($stmt->rowCount() == 1){
            $stats = $stmt->fetch();
            $forum_stats['forum_tot'] = $stats['tot_forum'];
            $forum_stats['topic_tot'] = $stats['tot_topic'];
            $forum_stats['forum_tot_cat'] = $stats['tot_cat'];
            // Carico i dati dell'utente nelle variabili di classe
            KLogger::instance("log", KLogger::DEBUG)->logInfo("Statistiche caricate");

        }else{
            
            return false;
        }
        return $forum_stats;
        
    }
    
    /**
     * Metodo getUsersStats: serve per ricavare statistiche relative agli utenti
     * @return boolean|array
     */
    public static function getUsersStats(){
        KLogger::instance("log", KLogger::DEBUG)->logInfo("Caricamento statistiche utenti ");
        
        $users_stat = array();

            $sql =  "select count(*) as tot_users,
                    (select count(*) from user where gender = 'm') as tot_maschi,
                    (select count(*) from user where gender = 'f') as tot_femmine,
                    (select round(avg(YEAR(NOW())-YEAR(date_of_birth))) from user) as eta_media,
                    (select round(avg(YEAR(NOW())-YEAR(date_of_birth))) from user where gender = 'm') as etamedia_maschi,
                    (select round(avg(YEAR(NOW())-YEAR(date_of_birth))) from user where gender = 'f') as etamedia_femmine
                    from user";

        $stmt = DB::getDBO()->prepare($sql);
        $stmt->execute();

        if($stmt->rowCount() == 1){
            $stats = $stmt->fetch();
            
            $users_stat['tot_users'] = ($stats['tot_users']==null)?0:$stats['tot_users'];
            $users_stat['tot_maschi'] =($stats['tot_maschi']==null)?0:$stats['tot_maschi'];
            $users_stat['tot_femmine'] = ($stats['tot_femmine']==null)?0:$stats['tot_femmine'];
            $users_stat['eta_media'] = ($stats['eta_media']==null)?0:$stats['eta_media'];
            $users_stat['etamedia_maschi'] = ($stats['etamedia_maschi']==null)?0:$stats['etamedia_maschi'];
            $users_stat['etamedia_femmine'] = ($stats['etamedia_femmine']==null)?0:$stats['etamedia_femmine'];

            // Carico i dati dell'utente nelle variabili di classe
            KLogger::instance("log", KLogger::DEBUG)->logInfo("Statistiche caricate");

        }else{
            
            return false;
        }
        return $users_stat;
        
    }
    
    /**
     * Metodo getUsersRegistrationData: serve per ricavare i dati per il grafico relativo alle iscrizioni degli utenti
     * @return boolean|array
     */
    public static function getUsersRegistrationData(){
        KLogger::instance("log", KLogger::DEBUG)->logInfo("Caricamento statistiche forum ");
        
        $user_reg = array();

        $sql = "select count(registration_date) as tot_reg, month(registration_date) as mese from user where month(registration_date) > month(now())-6
                group by month(registration_date)";

        $stmt = DB::getDBO()->prepare($sql);
        $stmt->execute();

        
        if($stmt->rowCount() != 0){
            while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $user_reg[] = array(Util::getMonth($data['mese']), $data['tot_reg']);
                
                
                
            }

            // Carico i dati dell'utente nelle variabili di classe
            KLogger::instance("log", KLogger::DEBUG)->logInfo("Utenti che compiono gli anni caricati");

        }else{
            
            return false;
        }
        
        $_SESSION['prova'] = serialize($user_reg);
        return $user_reg;
        
        
        
    }
    
    /**
     * Metodo getCatStats: serve per ricavare i dati per il grafico relativo alle categorie
     * @return boolean|array
     */
    public static function getCatStats(){
        KLogger::instance("log", KLogger::DEBUG)->logInfo("Caricamento statistiche forum ");
        
        $cat_stats = array();

        $sql =  "select category, IFNULL(LOG10(count(*)),0) as tot_forum,
                (select IFNULL(LOG10(count(id_topic)),0) from  topic, forum where forum_id = id_forum and f.category = category) as tot_topic,
                (select IFNULL(LOG10(count(id_post)),0) from forum, topic, post where id_forum = forum_id and id_topic = topic_id and f.category = category) as tot_post
                from forum f, category c
                where f.category = c.name and c.visible = 1
                group by f.category";

        $stmt = DB::getDBO()->prepare($sql);
        $stmt->execute();

        
        if($stmt->rowCount() != 0){
            while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $cat_stats[] = array($data['category'], $data['tot_forum'],$data['tot_topic'],$data['tot_post']);
            }
            

            // Carico i dati dell'utente nelle variabili di classe
            KLogger::instance("log", KLogger::DEBUG)->logInfo("Utenti che compiono gli anni caricati");

        }else{
            
            return false;
        }
        
        $_SESSION['cat_stats'] = serialize($cat_stats);
        return $cat_stats;
        
    }
}

?>
